﻿#-*-coding:utf-8-*-
from openpyxl.styles import colors,Font,Border,Side,PatternFill,Alignment
from openpyxl import load_workbook
from openpyxl import Workbook
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.chart import PieChart3D,Reference
import gc
#global wb
def _toUnicode(s, enc):
    return unicode(s, enc)

def creatBook():
    #global wb
    wb = Workbook()
    return wb
def loadBook(f):
    global wb
    wb = load_workbook(f)
    return wb
    
def freeze(wb,cell = 'A2'):
    sheet = wb.active
    sheet.freeze_panes = _toUnicode(cell,'gbk')
    
def writeArray(ary, enc='utf-8'):
    sheet = wb.active
    for i in range(1,len(ary)+1):
        for j in range(1,len(ary[i-1])+1):
           sheet.cell(row=i,column=j).value=_toUnicode(ary[i-1][j-1], enc)
 #italic=True
 
def writeSomeData(wb,mrow,mcolumn,data):
    sheet = wb.active
    if type(data) == type ('a'):
        sheet.cell(row=mrow,column=mcolumn).value=_toUnicode(data,'gbk')
    else:
        sheet.cell(row=mrow,column=mcolumn).value=data

 
def setAllFont(bjrow,bjcolumn,edrow,edcolumn,sname='Arial',scolor=colors.RED,sszie=20):
    sheet = wb.active
    for i in range(bjrow,edrow+1):
	    for j in range(bjcolumn,edcolumn+1):
		    sheet.cell(row=i,column=j).font= Font(name=_toUnicode(sname,'gbk'),color=scolor,size=sszie)
			
def setSheetName(wb,sheetname='Sheet'):
    sheet = wb.active		
    sheet.title = _toUnicode(sheetname,'gbk')
	
def saveSheet(wb,f='C:\\array.xlsx'):
    wb.save(f)
    del wb
    gc.collect()
    return 0
	
def getColumnData(mcolumn,sheetnum=0):
    url=[]
    sheetnames = wb.get_sheet_names()
    sheet  = wb.get_sheet_by_name(sheetnames[sheetnum])
    for rowNum in range(1,sheet.max_row+1):
        url.append(sheet.cell(row=rowNum,column=mcolumn).value)
    return url

def getRowData(mrow,sheetnum=0):
    url=[]
    sheetnames = wb.get_sheet_names()
    sheet = wb.get_sheet_by_name(sheetnames[sheetnum])
    for columnNum in range(1,sheet.max_column+1):
        url.append(sheet.cell(row=mrow,column=columnNum).value)
    return url

def getSomeData(mrow,mcolumn,sheetnum=0):
    sheetnames = wb.get_sheet_names()
    sheet = wb.get_sheet_by_name(sheetnames[sheetnum])
    return sheet.cell(row=mrow,column=mcolumn).value
    
def setRowHeight(mrow=1,mheight=30):
    sheet = wb.active
    sheet.row_dimensions[mrow].height = mheight

def setColumnWidth(wb,mcolumn=1,mwidth=30):
    sheet = wb.active
    sheet.column_dimensions[get_column_letter(mcolumn)].width = mwidth
	
def creatSheet(wb,sheetname='',loction=0):
	if loction == 0:
		sheet = wb.create_sheet()
	else:
		sheet = wb.create_sheet(0)
	if sheetname != '':
		sheet.title = _toUnicode(sheetname,'gbk')
		
def setActiveSheet(wb,index=0):
	if index >= 0:
		wb._active_sheet_index=index
		
def setActiveSheetByName(wb,sheetname):
	if sheetname != '':
		index = 0
		sheetnames = wb.get_sheet_names()
		for name in sheetnames:
			if name == _toUnicode(sheetname,'gbk'):
				wb._active_sheet_index=index
				return 1
			index += 1
	return 0
	
def mergeCells(wb,cells):
	sheet = wb.active
	sheet.merge_cells(_toUnicode(cells,'gbk'))
	
def alignment(wb,mrow,mcolumn,mhorizontal,mvertical):
	sheet = wb.active
	halignments = ('left','center','right')
	valignments = ('top','center','bottom')
	sheet.cell(row=mrow,column=mcolumn).alignment = Alignment(horizontal=halignments[mhorizontal],vertical=valignments[mvertical])
	
def pieChart(labelsCol,labelsRow,labelsMaxRow,dataCol,dataRow,dataMaxRow,stitle,location,wb):
	ws = wb.active
	pie = PieChart3D()
	labels = Reference(ws, min_col=labelsCol, min_row=labelsRow, max_row=labelsMaxRow)
	data = Reference(ws, min_col=dataCol, min_row=dataRow, max_row=dataMaxRow)
	pie.add_data(data, titles_from_data=True)
	pie.set_categories(labels)
	pie.title = _toUnicode(stitle,'gbk')
	ws.add_chart(pie, _toUnicode(location,'gbk'))
